﻿using Microsoft.Xrm.Sdk;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FFSupplier.Common.CustomException;
using FFSupplier.ServiceCore.Factory.Basic;

namespace FFSupplier.ServiceCore.Impl.CrmConnection
{
    public abstract class CrmDBConnection<T>
    {
        private SqlConnection connection;
        public CrmDBConnection()
        {
            connection = new SqlConnectionFactory().Create();

        }


        public T Single(string primaryKey)
        {
            T result = default(T);
            try
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandText = string.Format("select * from {0} where {0}id = '{1}'", typeof(T).Name, primaryKey);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        result = AsEntityEnumerable(reader).First();
                    }
                }
            }
            catch (Exception e)
            {
                throw new SqlExecuteException(e);
            }
            finally
            {
                connection.Close();
            }
            return result;
        }

        public List<T> Query(string sql)
        {
            List<T> result = new List<T>();
            try
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                PrepareCommand(command, connection, (SqlTransaction)null, CommandType.Text, sql, null);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        result = AsEntityEnumerable(reader);
                    }
                }
            }
            catch (Exception e)
            {
                throw new SqlExecuteException(e);
            }
            finally
            {
                connection.Close();
            }
            return result;
        }

        public List<T> Query(string sql, Hashtable commandParameters)
        {
            List<T> result = new List<T>();
            try
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                PrepareCommand(command, connection, (SqlTransaction)null, CommandType.Text, sql, commandParameters);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        result = AsEntityEnumerable(reader);
                    }
                }
            }
            catch (Exception e)
            {
                throw new SqlExecuteException(e);
            }
            finally
            {
                connection.Close();
            }
            return result;
        }

        public List<T> ExecStoredProcedure(string spName, Hashtable commandParameters, params SqlParameter[] paras)//传多个参数
        {
            List<T> result = new List<T>();
            try
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                PrepareCommand(command, connection, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);
                if (paras != null)
                {
                    foreach (var para in paras)
                    {
                        command.Parameters.Add(para);
                    }
                }
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        result = AsEntityEnumerable(reader);
                    }
                }
            }
            catch (Exception e)
            {
                throw new SqlExecuteException(e);
            }
            finally
            {
                connection.Close();
            }
            return result;
        }

        public int ExecStoredProcedureCount(string spName, Hashtable commandParameters)
        {
            int result = 0;
            try
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                PrepareCommand(command, connection, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        var count = reader[0];
                        if (count != DBNull.Value)
                        {
                            int.TryParse(count.ToString(), out result);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                throw new SqlExecuteException(e);
            }
            finally
            {
                connection.Close();
            }
            return result;
        }

        public void ExecStoredProcedureNoQuery(string spName, Hashtable commandParameters, params SqlParameter[] paras)
        {
            try
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                PrepareCommand(command, connection, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);
                //using (SqlDataReader reader = command.ExecuteReader())
                //{
                //    if (reader.HasRows)
                //    {
                //        result = AsEntityEnumerable(reader);
                //    }
                //}
                if (paras != null)
                {
                    foreach (var para in paras)
                    {
                        command.Parameters.Add(para);
                    }
                }
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new SqlExecuteException(e);
            }
            finally
            {
                connection.Close();
            }
        }

        public List<T> QueryPagination(string sql,
                                       Hashtable commandParameters,
                                       string orderColumnNames,
                                       int itemsPerPage,
                                       int currentPage,
                                       out int totalItems)
        {
            List<T> result = new List<T>();
            totalItems = 0;
            currentPage = currentPage > 0 ? currentPage : 1;
            itemsPerPage = itemsPerPage > 1 ? itemsPerPage : 2;
            try
            {

                connection.Open();
                SqlCommand command = connection.CreateCommand();
                string countSQL = string.Format("select count(*) from ({0}) t", sql);
                PrepareCommand(command, connection, (SqlTransaction)null, CommandType.Text, countSQL, commandParameters);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        totalItems = (int)reader[0];
                    }
                }
                if (totalItems > 0)
                {
                    command = connection.CreateCommand();
                    string paginationSQL = string.Format(@"
                        select * from
                        (
                            select 
		                        ROW_NUMBER() over(order by {0}) as number, 
                                *
                            from
                            (
                                {1}
                            ) top2
                        ) top1
                        where number >= {2} and number < {3} "
                    , orderColumnNames
                    , sql
                    , ((currentPage - 1) * itemsPerPage) + 1
                    , (currentPage * itemsPerPage) + 1);
                    PrepareCommand(command, connection, (SqlTransaction)null, CommandType.Text, paginationSQL, commandParameters);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            result = AsEntityEnumerable(reader);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                throw new SqlExecuteException(e);
            }
            finally
            {
                connection.Close();
            }

            return result;
        }

        /// <summary>
        /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
        /// to the provided command.
        /// </summary>
        /// <param name="command">the SqlCommand to be prepared</param>
        /// <param name="connection">a valid SqlConnection, on which to execute this command</param>
        /// <param name="transaction">a valid SqlTransaction, or 'null'</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, Hashtable commandParameters)
        {
            //if the provided connection is not open, we will open it
            if (connection.State != System.Data.ConnectionState.Open)
            {
                connection.Open();
            }
            command.Connection = connection;
            command.CommandText = commandText;
            if (transaction != null)
            {
                command.Transaction = transaction;
            }
            command.CommandType = commandType;
            command.CommandTimeout = connection.ConnectionTimeout;
            if (commandParameters != null)
            {
                foreach (var key in commandParameters.Keys)
                {
                    if (commandParameters[key] is Guid)
                    {
                        Guid id = (Guid)commandParameters[key];
                        command.Parameters.AddWithValue(key.ToString(), Guid.Empty.Equals(id) ? DBNull.Value : commandParameters[key]);
                    }
                    else
                    {
                        command.Parameters.AddWithValue(key.ToString(), commandParameters[key] == null ? DBNull.Value : commandParameters[key]);
                    }
                }
            }
            return;
        }

        public DataTable ExecStoredProcedureGetDateTable(string spName, Hashtable commandParameters, params SqlParameter[] paras)//传多个参数
        {
            DataTable result = new DataTable();
            try
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                PrepareCommand(command, connection, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);
                if (paras != null)
                {
                    foreach (var para in paras)
                    {
                        command.Parameters.Add(para);
                    }
                }
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        result.Load(reader);
                        ///动态添加表的数据列  
                        //for (int i = 0; i < reader.FieldCount; i++)
                        //{
                        //    DataColumn myDataColumn = new DataColumn();
                        //    myDataColumn.DataType = reader.GetFieldType(i);
                        //    myDataColumn.ColumnName = reader.GetName(i);
                        //    result.Columns.Add(myDataColumn);
                        //}
                        /////添加表的数据  
                        //while (reader.Read())
                        //{
                        //    DataRow myDataRow = result.NewRow();
                        //    for (int i = 0; i < reader.FieldCount; i++)
                        //    {
                        //        myDataRow[i] = reader[i].ToString();
                        //    }
                        //    result.Rows.Add(myDataRow);
                        //    myDataRow = null;
                        //}
                    }
                }
            }
            catch (Exception e)
            {
                throw new SqlExecuteException(e);
            }
            finally
            {
                connection.Close();
            }
            return result;
        }



        #region Entity Convert
        private List<T> AsEntityEnumerable(SqlDataReader reader)
        {
            DataTable table = new DataTable();
            table.Load(reader);
            return table.AsEnumerable().Select(row =>
            {
                return GenerateEntity(row);
            }).ToList();
        }

        protected abstract T GenerateEntity(DataRow row);

        protected string GetCellStringValue(object cell)
        {
            if (cell != DBNull.Value)
            {
                return cell.ToString();
            }
            return null;
        }
        protected Money GetCellMoneyValue(object cell)
        {
            Money result = new Money(0);
            if (cell != DBNull.Value)
            {
                decimal value;
                if (decimal.TryParse(cell.ToString(), out value))
                {
                    result = new Money(value);
                }
            }
            return result;
        }

        protected decimal GetCellDecimalValue(object cell)
        {
            decimal result = new decimal(0);
            if (cell != DBNull.Value)
            {
                decimal.TryParse(cell.ToString(), out result);
            }
            return result;
        }
        protected int GetCellIntValue(object cell)
        {
            int result = 0;
            if (cell != DBNull.Value)
            {
                int.TryParse(cell.ToString(), out result);
            }
            return result;
        }
        protected int? GetCellIntValueNullable(object cell)
        {
            int? result = null;
            int val = 0;
            if (cell != DBNull.Value)
            {
                int.TryParse(cell.ToString(), out val);
                result = val;
            }
            return result;
        }

        protected float GetCellFloatValue(object cell)
        {
            float result = new float();
            if (cell != DBNull.Value)
            {
                float.TryParse(cell.ToString(), out result);
            }
            return result;
        }
        protected float? GetCellFloatValueNullable(object cell)
        {
            float? result = null;
            float value;
            if (cell != DBNull.Value)
            {
                float.TryParse(cell.ToString(), out value);
                result = value;
            }
            return result;
        }
        protected double GetCellDoubleValue(object cell)
        {
            double result = new double();
            if (cell != DBNull.Value)
            {
                double.TryParse(cell.ToString(), out result);
            }
            return result;
        }

        protected double? GetCellDoubleValueNullable(object cell)
        {
            double? result = null;
            double value;
            if (cell != DBNull.Value)
            {
                double.TryParse(cell.ToString(), out value);
                result = value;
            }
            return result;
        }

        protected bool GetCellBoolValue(object cell)
        {
            bool result = false;
            if (cell != DBNull.Value)
            {
                bool.TryParse(cell.ToString(), out result);
            }
            return result;
        }

        protected bool? GetCellBoolValueNullable(object cell)
        {
            bool? result = null;
            bool val = false;
            if (cell != DBNull.Value)
            {
                bool.TryParse(cell.ToString(), out val);
                result = val;
            }
            return result;
        }
        protected Guid GetCellGuidValue(object cell)
        {
            Guid result = Guid.Empty;
            if (cell != DBNull.Value)
            {
                Guid.TryParse(cell.ToString(), out result);
            }
            return result;
        }
        protected OptionSetValue GetCellOptionSetValue(object cell)
        {
            OptionSetValue result = null;
            if (cell != DBNull.Value)
            {
                int value;
                if (int.TryParse(cell.ToString(), out value))
                {
                    result = new OptionSetValue(value);
                }
            }
            return result;
        }
        protected EntityReference GetCellEntityReferenceValue(object cellId, string entityLogicalName)
        {
            EntityReference result = null;
            if (cellId != DBNull.Value)
            {
                Guid value;
                if (Guid.TryParse(cellId.ToString(), out value))
                {
                    result = new EntityReference()
                    {
                        Id = value,
                        LogicalName = entityLogicalName
                    };
                }
            }
            return result;
        }
        protected EntityReference GetCellEntityReferenceValue(object cellId, object cellName, string entityLogicalName)
        {
            EntityReference result = null;
            if (cellId != DBNull.Value)
            {
                Guid value;
                if (Guid.TryParse(cellId.ToString(), out value))
                {
                    result = new EntityReference()
                    {
                        Id = value,
                        Name = GetCellStringValue(cellName),
                        LogicalName = entityLogicalName
                    };
                }
            }
            return result;
        }

        protected E GetCellEnumValue<E>(object cell)
        {
            int val = GetCellIntValue(cell);
            if (Enum.IsDefined(typeof(E), val))
            {
                return (E)Enum.ToObject(typeof(E), val);
            }
            return (E)Enum.ToObject(typeof(E), int.MaxValue);
        }

        protected DateTime GetCellDateTimeValue(object cell)
        {
            DateTime result = DateTime.MinValue;
            if (cell != DBNull.Value)
            {
                DateTime.TryParse(cell.ToString(), out result);
            }
            return result;
        }
        protected DateTime? GetCellDateTimeValueNullable(object cell)
        {
            DateTime? result = null;
            DateTime val;
            if (cell != DBNull.Value)
            {
                DateTime.TryParse(cell.ToString(), out val);
                result = val;
            }
            return result;
        }
        #endregion
    }
}
